﻿using Model;
using System;
using System.Data;
using System.Text;

namespace BLL
{
    public class QS_PlanDetail_DropLIstBLL : BaseBLL
    {
        #region Select查询所有

        /// <summary>
        /// 查询资金项目列表
        /// </summary>
        /// <returns></returns>
        public DataTable SelectPlanDropList(string where, decimal proId = -1)
        {
            var sql = string.Format("SELECT * FROM QS_PLANDETAIL_DROPLIST WHERE N_CMYID = " + User.USER_COMPANY);
            sql += where;
            //sql += " AND N_PRIVATE = " + proId;
            var dt = Query(sql);
            if (dt.Rows.Count <= 0)
            {
                var sql2 = string.Format("SELECT * FROM QS_PLANDETAIL_DROPLIST WHERE N_CMYID = 0");
                sql2 += where;
                //判断是否是表一
                //if (proId != -1)
                //{
                //QS_PROJECTBLL project = new QS_PROJECTBLL();
                //string proName = project.Find(proId).C_PRONAME;
                //proId = project.FindByName(proName, 0).N_PROID;
                //}
                dt = Query(sql2 + " AND N_PRIVATE = " + proId);
                if (dt.Rows.Count <= 0)
                {
                    dt = Query(sql2 + " AND N_PRIVATE = -1");
                }
            }
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            return dt;
        }

        /// <summary>
        /// 查询用户类别
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable SelectUserClass(string where)
        {
            var sql = "SELECT * FROM QS_PLANDETAILS_USERCLASS WHERE 1=1";
            sql += " AND C_REMARK='" + where + "'";
            var dt = Query(sql);
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            return dt;
        }

        /// <summary>
        /// 查询款项类别
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataTable SelectPaymentCategory(string where)
        {
            var sql = "SELECT * FROM QS_PLANDETAILS_PAYMENTCATEGORY WHERE 1=1";
            sql += " AND C_REMARK='" + where + "'";
            var dt = Query(sql);
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            return dt;
        }

        /// <summary>
        /// 查询款项用途
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataTable SelectPaymentPurpose(string where)
        {
            var sql = "SELECT * FROM QS_PLANDETAILS_PAYMENTPURPOSE WHERE 1=1";
            sql += " AND C_REMARK='" + where + "'";
            var dt = Query(sql);
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            return dt;
        }

        /// <summary>
        /// 查询项目名称
        /// </summary>
        /// <returns></returns>
        public DataTable SelectProjectName(string where, decimal proId)
        {
            var sql = "SELECT * FROM QS_PLANDETAILS_PROJECTNAME WHERE 1=1";
            sql += " AND C_REMARK='" + where + "'";
            sql += " AND N_PRIVATE=" + proId;
            var dt = Query(sql);
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            return dt;
        }

        #endregion Select查询所有

        #region Find根据条件查询

        public QS_PlanDetail_DropLIst FindPlanDropList(decimal id)
        {
            var sql = "SELECT * FROM QS_PLANDETAIL_DROPLIST WHERE N_ID = {0}";
            sql = string.Format(sql, id);
            return ConvertToPlanDropList(RowSingeleOfNull(sql));
        }

        private QS_PlanDetail_DropLIst ConvertToPlanDropList(DataRow row)
        {
            if (row == null)
            {
                return null;
            }
            var entity = new QS_PlanDetail_DropLIst
            {
                N_ID = Convert.ToDecimal(row["N_ID"]),
                C_NAME = Convert.ToString(row["C_NAME"]),
                N_CMYID = Convert.ToDecimal(row["N_CMYID"]),
                C_TABLETYPE = Convert.ToString(row["C_TABLETYPE"]),
                N_NAMETYPE = Convert.ToDecimal(row["N_NAMETYPE"]),
                N_PRIVATE = Convert.ToDecimal(row["N_PRIVATE"]),
                C_REMARK = Convert.ToString(row["C_REMARK"])
            };
            return entity;
        }

        public QS_PLANDETAILS_USERCLASS FindUSERCLASS(decimal id)
        {
            var sql = "SELECT * FROM QS_PLANDETAILS_USERCLASS WHERE N_ID = {0}";
            sql = string.Format(sql, id);
            return ConvertToUSERCLASS(RowSingeleOfNull(sql));
        }

        private QS_PLANDETAILS_USERCLASS ConvertToUSERCLASS(DataRow row)
        {
            if (row == null)
            {
                return null;
            }
            var entity = new QS_PLANDETAILS_USERCLASS
            {
                N_ID = Convert.ToDecimal(row["N_ID"]),
                C_NAME = Convert.ToString(row["C_NAME"]),
                C_REMARK = Convert.ToString(row["C_REMARK"])
            };
            return entity;
        }

        public QS_PLANDETAILS_PAYMENTCATEGORY FindPAYMENTCATEGORY(decimal id)
        {
            var sql = "SELECT * FROM QS_PLANDETAILS_PAYMENTCATEGORY WHERE N_ID = {0}";
            sql = string.Format(sql, id);
            return ConvertToPAYMENTCATEGORY(RowSingeleOfNull(sql));
        }

        private QS_PLANDETAILS_PAYMENTCATEGORY ConvertToPAYMENTCATEGORY(DataRow row)
        {
            if (row == null)
            {
                return null;
            }
            var entity = new QS_PLANDETAILS_PAYMENTCATEGORY
            {
                N_ID = Convert.ToDecimal(row["N_ID"]),
                C_NAME = Convert.ToString(row["C_NAME"]),
                C_REMARK = Convert.ToString(row["C_REMARK"])
            };
            return entity;
        }

        public QS_PLANDETAILS_PAYMENTPURPOSE FindPAYMENTPURPOSE(decimal id)
        {
            var sql = "SELECT * FROM QS_PLANDETAILS_PAYMENTPURPOSE WHERE N_ID = {0}";
            sql = string.Format(sql, id);
            return ConvertToPAYMENTPURPOSE(RowSingeleOfNull(sql));
        }

        private QS_PLANDETAILS_PAYMENTPURPOSE ConvertToPAYMENTPURPOSE(DataRow row)
        {
            if (row == null)
            {
                return null;
            }
            var entity = new QS_PLANDETAILS_PAYMENTPURPOSE
            {
                N_ID = Convert.ToDecimal(row["N_ID"]),
                C_NAME = Convert.ToString(row["C_NAME"]),
                C_REMARK = Convert.ToString(row["C_REMARK"])
            };
            return entity;
        }

        public QS_PLANDETAILS_PROJECTNAME FindPROJECTNAME(decimal id)
        {
            var sql = "SELECT * FROM QS_PLANDETAILS_PROJECTNAME WHERE N_ID = {0}";
            sql = string.Format(sql, id);
            return ConvertToPROJECTNAME(RowSingeleOfNull(sql));
        }

        private QS_PLANDETAILS_PROJECTNAME ConvertToPROJECTNAME(DataRow row)
        {
            if (row == null)
            {
                return null;
            }
            var entity = new QS_PLANDETAILS_PROJECTNAME
            {
                N_ID = Convert.ToDecimal(row["N_ID"]),
                C_NAME = Convert.ToString(row["C_NAME"]),
                C_REMARK = Convert.ToString(row["C_REMARK"]),
                N_PRIVATE = Convert.ToDecimal(row["N_PRIVATE"])
            };
            return entity;
        }

        #endregion Find根据条件查询

        public DataTable GetData(string sqlStr, string orderbyStr, int skip, int take, out int count)
        {
            var sqlBuilder = new StringBuilder();
            sqlBuilder.AppendLine(sqlStr);

            if (orderbyStr != null)
            {
                sqlBuilder.AppendLine(" ORDER BY " + orderbyStr);
            }
            count = SqlCount(sqlBuilder.ToString());
            return PageQuery(sqlBuilder.ToString(), skip, take);
        }

        #region UPDATE

        public bool UpdatePlanDropList(QS_PlanDetail_DropLIst entity, string operaname)
        {
            var sql = @"update qs_plandetail_droplist
   set c_name      = '{0}',
       n_cmyid     = {1},
       c_tabletype = '{2}',
       n_nametype  = {3},
       n_private   = {4}
 where n_id = {5}";
            sql = string.Format(sql, entity.C_NAME, entity.N_CMYID, entity.C_TABLETYPE, entity.N_NAMETYPE, entity.N_PRIVATE, entity.N_ID);
            return NoQuery(sql, operaname) > 0 ? true : false;
        }

        public bool UpdateUSERCLASS(QS_PLANDETAILS_USERCLASS entity, string operaname)
        {
            var sql = @"update qs_plandetails_userclass
   set c_name = '{0}', c_remark = '{1}' WHERE N_ID = {2}";
            sql = string.Format(sql, entity.C_NAME, entity.C_REMARK, entity.N_ID);
            return NoQuery(sql, operaname) > 0 ? true : false;
        }

        public bool UpdatePAYMENTCATEGORY(QS_PLANDETAILS_PAYMENTCATEGORY entity, string operaname)
        {
            var sql = @"update QS_PLANDETAILS_PAYMENTCATEGORY
   set c_name = '{0}', c_remark = '{1}' WHERE N_ID = {2}";
            sql = string.Format(sql, entity.C_NAME, entity.C_REMARK, entity.N_ID);
            return NoQuery(sql, operaname) > 0 ? true : false;
        }

        public bool UpdatePAYMENTPURPOSE(QS_PLANDETAILS_PAYMENTPURPOSE entity, string operaname)
        {
            var sql = @"update QS_PLANDETAILS_PAYMENTPURPOSE
   set c_name = '{0}', c_remark = '{1}' WHERE N_ID = {2}";
            sql = string.Format(sql, entity.C_NAME, entity.C_REMARK, entity.N_ID);
            return NoQuery(sql, operaname) > 0 ? true : false;
        }

        public bool UpdatePROJECTNAME(QS_PLANDETAILS_PROJECTNAME entity, string operaname)
        {
            var sql = @"update QS_PLANDETAILS_PROJECTNAME
   set c_name = '{0}', c_remark = '{1}',N_PRIVATE={2} WHERE N_ID = {3}";
            sql = string.Format(sql, entity.C_NAME, entity.C_REMARK, entity.N_PRIVATE, entity.N_ID);
            return NoQuery(sql, operaname) > 0 ? true : false;
        }

        #endregion UPDATE

        #region INSERT

        public bool InsertPlanDropList(QS_PlanDetail_DropLIst entity, string operaname)
        {
            entity.N_ID = GetSequence("QS_DROPLIST_ID");
            var sql = @"insert into qs_plandetail_droplist
  (n_id, c_name, n_cmyid, c_tabletype, n_nametype, n_private)
values
  ({0}, '{1}', {2}, '{3}', {4}, {5})";
            sql = string.Format(sql, entity.N_ID, entity.C_NAME, entity.N_CMYID, entity.C_TABLETYPE, entity.N_NAMETYPE, entity.N_PRIVATE);
            return NoQuery(sql, operaname) > 0 ? true : false;
        }

        public bool InsertUSERCLASS(QS_PLANDETAILS_USERCLASS entity, string operaname)
        {
            entity.N_ID = GetSequence("SEQ_USERCLASS_ID");
            var sql = @"insert into QS_PLANDETAILS_USERCLASS
  (n_id, c_name, c_remark,N_CMYID)
values
  ({0}, '{1}', '{2}',{3})";
            sql = string.Format(sql, entity.N_ID, entity.C_NAME, entity.C_REMARK, User.USER_COMPANY);
            return NoQuery(sql, operaname) > 0 ? true : false;
        }

        public bool InsertPAYMENTCATEGORY(QS_PLANDETAILS_PAYMENTCATEGORY entity, string operaname)
        {
            entity.N_ID = GetSequence("SEQ_PAYMENTCATEGORY_ID");
            var sql = @"insert into QS_PLANDETAILS_PAYMENTCATEGORY
  (n_id, c_name, c_remark,N_CMYID)
values
  ({0}, '{1}', '{2}',{3})";
            sql = string.Format(sql, entity.N_ID, entity.C_NAME, entity.C_REMARK, User.USER_COMPANY);
            return NoQuery(sql, operaname) > 0 ? true : false;
        }

        public bool InsertPAYMENTPURPOSE(QS_PLANDETAILS_PAYMENTPURPOSE entity, string operaname)
        {
            entity.N_ID = GetSequence("SEQ_PAYMENTPURPOSE_ID");
            var sql = @"insert into QS_PLANDETAILS_PAYMENTPURPOSE
  (n_id, c_name, c_remark,N_CMYID)
values
  ({0}, '{1}', '{2}',{3})";
            sql = string.Format(sql, entity.N_ID, entity.C_NAME, entity.C_REMARK, User.USER_COMPANY);
            return NoQuery(sql, operaname) > 0 ? true : false;
        }

        public bool InsertPROJECTNAME(QS_PLANDETAILS_PROJECTNAME entity, string operaname)
        {
            entity.N_ID = GetSequence("SEQ_PROJECTNAME_ID");
            var sql = @"insert into QS_PLANDETAILS_PROJECTNAME
  (n_id, c_name, c_remark,N_CMYID,N_PRIVATE)
values
  ({0}, '{1}', '{2}',{3},{4})";
            sql = string.Format(sql, entity.N_ID, entity.C_NAME, entity.C_REMARK, User.USER_COMPANY, entity.N_PRIVATE);
            return NoQuery(sql, operaname) > 0 ? true : false;
        }

        #endregion INSERT

        #region delete

        public int DELETE(string ids, string operName)
        {
            string sql = string.Format(@"DELETE FROM QS_PLANDETAIL_DROPLIST WHERE N_ID IN(" + ids + ")");
            int count = NoQuery(sql, operName);
            return count;
        }

        #endregion delete
    }
}